﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;

namespace Portal.CMS
{
    public class HtmlBLL
    {
        string pc = "vvvxxx!@#$abc*()";
        public HtmlCls CreateModel(string ActiveLanguage, string HtmlId)
        {
            return CreateModel(Environments.ConnectionString, ActiveLanguage, HtmlId);
        }

        public HtmlCls CreateModel(string ConnectionString,string ActiveLanguage,string HtmlId)
        {
            string Query = "select * from TableHtml where HtmlId=@HtmlId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                HtmlCls HtmlCls = new HtmlCls();
                HtmlCls.HtmlId = (string)dtResult.Rows[0]["HtmlId"];
                try
                {
                    HtmlCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
                try
                {
                    HtmlCls.Alias = (string)dtResult.Rows[0]["Alias"];
                }
                catch { }
                try
                {
                    HtmlCls.Body = (string)dtResult.Rows[0][ActiveLanguage + "Body"];
                }
                catch { }
                try
                {
                    HtmlCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }
                try
                {
                    HtmlCls.EcryptSubject = (string)dtResult.Rows[0][ActiveLanguage + "EcryptSubject"];
                }
                catch { }
                try
                {
                    HtmlCls.EcryptBody = (string)dtResult.Rows[0][ActiveLanguage + "EcryptBody"];
                }
                catch { }
                HtmlCls.EntryDate = (DateTime)dtResult.Rows[0]["EntryDate"];
                try
                {
                    HtmlCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                return HtmlCls;
            }
        }


        public DataTable Reading(string belongSite,string ActiveLanguage,string WorkFlowStatus)
        {
            string Query = "select HtmlId,Alias=IsNull(Alias,''),Subject=" + ActiveLanguage + "Subject,EntryDate,WorkflowStatus,IsPublishing=IsNull(IsPublishing,0) from TableHtml where WorkFlowStatus=@WorkFlowStatus and belongSite='" + belongSite + "' order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@WorkFlowStatus", SqlDbType.VarChar);
            sqlCommand.Parameters["@WorkFlowStatus"].Value = WorkFlowStatus;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForOne(string belongSite, string ActiveLanguage, string HtmlId)
        {
            string Query = "select * from TableHtml where HtmlId=@HtmlId order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForSync(string belongSite)
        {
            string Query = "select * from TableHtml where belongSite='" + belongSite + "' ";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForSyncFromTool(string ConnectionString, string belongSite)
        {
            string Query = "select HtmlId from TableHtml where belongSite='" + belongSite + "' ";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForService(string ActiveLanguage)
        {
            string Query = "select Id=HtmlId,Subject="+ActiveLanguage+"Subject from TableHtml order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }



        public DataTable ReadingWorkflowStatus(string belongSite)
        {
            string Query = "select distinct WorkflowStatus from TableHtml where belongSite='" + belongSite+"'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddHtml(SqlTransaction sqlTransaction,string belongSite, string ActiveLanguage,string Alias,string Subject, string Body)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string HtmlId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableHtml(HtmlId,belongSite,Alias,Subject," + ActiveLanguage + "Subject," + ActiveLanguage + "Body," + ActiveLanguage + "EcryptSubject," + ActiveLanguage + "EcryptBody) values (@HtmlId,@belongSite,@Alias,@Subject,@Subject,@Body,@EcryptSubject,@EcryptBody)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Alias"].Value = Alias;


            sqlCommand.Parameters.Add("@Body", SqlDbType.NText);
            sqlCommand.Parameters["@Body"].Value = Body;

            sqlCommand.Parameters.Add("@EcryptSubject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@EcryptSubject"].Value = new RSACSPCls().Encrypt(pc,Subject);

            sqlCommand.Parameters.Add("@EcryptBody", SqlDbType.NText);
            sqlCommand.Parameters["@EcryptBody"].Value = new RSACSPCls().Encrypt(pc,Body);

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            try
            {
                sqlCommand.ExecuteNonQuery();

                //bool UseWorkflowTask=false;
                //try
                //{
                //    UseWorkflowTask = bool.Parse(Environments.GetAppSettings("UseWorkflowTask"));
                //}
                //catch { }
                //if (UseWorkflowTask)
                //{
                //    string FileWorkFlow = Environment.Environments.AdminPathRoot + "\\WorkFlows\\Html.xml";
                //    string PreviewUrl = Screen.BuildViewUrl(new ParamCls[]{
                //    new ParamCls("WebPartId",new HtmlPreview().WebPartId)
                //});
                //    string WorkflowActionId=System.Guid.NewGuid().ToString();
                //    new WorkFlowLibrary.WorkFlowApp(FileWorkFlow, Environment.Environments.ConnectionString).StartWorkFlow(
                //      sqlTransaction,
                //      HtmlId,
                //      Environment.Environments.CurrentLoginUser.LoginName,
                //      Environment.Environments.CurrentLoginUser.LoginName,
                //      Environment.Environments.CurrentLoginUser.OfficeCode,
                //      HtmlId,
                //      Subject,
                //      "",
                //      PreviewUrl);
                //}

                //doan nay call webservice
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    DataSet ds = new DataSet("xml");
                //    ds.Tables.Add("Html");
                //    ds.Tables[0].Columns.Add("belongSite");
                //    ds.Tables[0].Columns.Add("ActiveLanguage");
                //    ds.Tables[0].Columns.Add("HtmlId");
                //    ds.Tables[0].Columns.Add("Subject");
                //    ds.Tables[0].Columns.Add("Body");
                //    ds.Tables[0].Columns.Add("Owner");
                //    ds.Tables[0].Columns.Add("EntryDate");
                //    ds.Tables[0].Columns.Add("Alias");

                //    ds.Tables[0].Rows.Add(new object[]{
                //        belongSite,ActiveLanguage, HtmlId,Subject,Body,Environments.CurrentLoginUser.LoginName,System.DateTime.Now,Alias
                //    });

                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].AddHtml(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return HtmlId;
        }



        public void SaveHtml(
            SqlTransaction sqlTransaction, 
            string belongSite,
            string HtmlId,
            string ActiveLanguage,
            string Alias,
            string Subject, 
            string Body)
        {
            HtmlCls HtmlCls = new HtmlBLL().CreateModel(ActiveLanguage,HtmlId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableHtml set Alias=@Alias," + ActiveLanguage + "Subject=@Subject," + ActiveLanguage + "Body=@Body," + ActiveLanguage + "EcryptSubject=@EcryptSubject," + ActiveLanguage + "EcryptBody=@EcryptBody where HtmlId=@HtmlId;" +
                           " Insert into TableHtmlHistory(HtmlHistoryId,fk_Html_Id,LogDate, LogUser, LogFullName) values (newid(),@HtmlId,getdate(), @LogUser, @LogFullName)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
           
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Alias"].Value = Alias;


            sqlCommand.Parameters.Add("@Body", SqlDbType.NText);
            sqlCommand.Parameters["@Body"].Value = Body;

            sqlCommand.Parameters.Add("@EcryptSubject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@EcryptSubject"].Value = new RSACSPCls().Encrypt(pc,Subject);

            sqlCommand.Parameters.Add("@EcryptBody", SqlDbType.NText);
            sqlCommand.Parameters["@EcryptBody"].Value = new RSACSPCls().Encrypt(pc,Body);

            sqlCommand.Parameters.Add("@LogUser", SqlDbType.NVarChar);
            sqlCommand.Parameters["@LogUser"].Value = Environments.CurrentLoginUser.LoginName;

            sqlCommand.Parameters.Add("@LogFullName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@LogFullName"].Value = Environments.CurrentLoginUser.FullName;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    DataSet ds = new DataSet("xml");
                //    ds.Tables.Add("Html");
                //    ds.Tables[0].Columns.Add("belongSite");
                //    ds.Tables[0].Columns.Add("HtmlId");
                //    ds.Tables[0].Columns.Add("ActiveLanguage");
                //    ds.Tables[0].Columns.Add("Subject");
                //    ds.Tables[0].Columns.Add("Body");
                //    ds.Tables[0].Columns.Add("EntryDate");
                //    ds.Tables[0].Columns.Add("Alias");

                //    ds.Tables[0].Rows.Add(new object[]{
                //        belongSite,HtmlId,ActiveLanguage, Subject,Body,HtmlCls.EntryDate,Alias
                //    });

                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].SaveHtml(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void PublishingHtml(
            SqlTransaction sqlTransaction,
            string HtmlId)
        {
            string Query = "";
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            Query = " Update TableHtml set IsPublishing=1,WorkflowStatus='Approved' where HtmlId=@HtmlId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;


            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SyncWithServer(
             string ConnectionString,
             string HtmlId,
             bool   UseWsForDeploy,
             string ActiveLanguage,
             string PathRoot,
             string WorkingPath,
             string PortalAlias,
             string WsUrl,
             string WsSecurityCode)
        {
            if (UseWsForDeploy)
            {
                HtmlCls HtmlCls = new HtmlBLL().CreateModel(ConnectionString,ActiveLanguage, HtmlId);
                string Query =
                    " select * from TableHtml where HtmlId=@HtmlId";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@HtmlId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@HtmlId"].Value = HtmlId;
                DataSet ds = new DataSet();
                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableHtml";
                ds.Tables["TableHtml"].Rows[0]["IsPublishing"] = 1;
                string Body = (string)ds.Tables["TableHtml"].Rows[0][ActiveLanguage + "Body"];
                Body = Portal.Environment.ReplateTextBll.ReplaceText(PathRoot,PortalAlias, Body);
                ds.Tables["TableHtml"].Rows[0][ActiveLanguage + "Body"] = Body;

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    HtmlId,
                    HtmlCls.belongSite,
                    XmlData,
                    null,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.Html);
                if (oWsReturnParamCls.Error)
                {
                    throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
                }
            }
        }

        public void SyncWithServer(string HtmlId)
        {
            string WsUrl = Environments.GetConfigFromXml("AppWs");
            string WsSecurityCode = Portal.Environment.Environments.WsSecurityCode;
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            SyncWithServer(
                    Environments.ConnectionString,
                    HtmlId,
                    UseWsForDeploy,
                    Environments.ActiveLanguage,
                    Environments.PathRoot,
                    Environments.WorkingPath,
                    Environments.PortalAlias,
                    WsUrl,
                    WsSecurityCode
                );
        }

        public void UnPublishingHtml(
            SqlTransaction sqlTransaction,
            string HtmlId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableHtml set IsPublishing=0,WorkflowStatus='UnPublishing' where HtmlId=@HtmlId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
        

        public void DeleteHtml(SqlTransaction sqlTransaction, string HtmlId)
        {
            HtmlCls HtmlCls = new HtmlBLL().CreateModel(Environments.ActiveLanguage, HtmlId);
          
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            Environment.Permission.DeletePermission(sqlTransaction, HtmlId);

            string Query =
                           " Delete from TableHtmlHistory where fk_Html_Id=@HtmlId;"+
                           " Delete from TableHtml where HtmlId=@HtmlId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@HtmlId", SqlDbType.VarChar);
            sqlCommand.Parameters["@HtmlId"].Value = HtmlId;

            try
            {
                sqlCommand.ExecuteNonQuery();

                string SubjectDeleted = "";
                if (HtmlCls != null)
                {
                    SubjectDeleted = HtmlCls.Subject;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableHtml","Bài viết (html)", HtmlId,SubjectDeleted);
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
    }
}
